﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;


namespace cselstad_poc
{
    class AddressEntryDAO
    {
        SqlConnection connection = new SqlConnection();

        public void ConnectDatabase(string connectionString)
        {
            connection.ConnectionString = connectionString;
            connection.Open();
        }
        public void CloseDatabase()
        {
            connection.Close();
        }

        public DataTable LoadRecords(string connectionString)
        {
            string cmd = "SELECT * FROM AddressEntry";
            SqlDataAdapter ad = new SqlDataAdapter(cmd, connectionString);

            DataTable dt = new DataTable();
            ad.Fill(dt);

            return dt;            
        }

        public void UpdateRecord(AddressEntry entry, AddressEntry newentry)
        {
            string oname = entry.GetName();
            string oemail = entry.GetEmail();
            string nname = newentry.GetName();
            string nemail = newentry.GetEmail();

            SqlCommand command = new SqlCommand("UPDATE AddressEntry SET Name='" + 
               nname + "', Email='" + nemail + "' WHERE Name='" + oname +
               "' AND Email='" + oemail + "'", connection);
            command.ExecuteNonQuery();

        }

        public void CreateRecord(AddressEntry entry)
        {
            string name = entry.GetName();
            string email = entry.GetEmail();
            
            SqlCommand command = new SqlCommand("INSERT INTO AddressEntry(Name, Email) " +
                            "Values('" + name + "', '" + email + "')", connection);
            command.ExecuteNonQuery();
        }

        public void DeleteRecord(AddressEntry entry)
        {
            string name = entry.GetName();
            string email = entry.GetEmail();

            SqlCommand command = new SqlCommand("DELETE FROM AddressEntry WHERE " +
                        "Name= '" + name + "' AND email ='" + email + "'", connection);
            command.ExecuteNonQuery();
        }
    }
}
